{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "59271992-251c-4f1b-9dd7-3f4ef93cf25d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "aadf441b-bf55-4d13-aa11-1e0bbc9586f3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_parquet(\"stock_trades.parquet\")\n",
    "df = df.sort(\"交易日期\", \"交易时间\", \"证券代码\")\n",
    "df = df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String),\n",
    "    手续费率=pl.col(\"手续费\") / pl.col(\"成交金额\"),\n",
    "    印花税率=pl.col(\"印花税\") / pl.col(\"成交金额\"),\n",
    "    过户费率=pl.col(\"过户费\") / pl.col(\"成交金额\"),\n",
    ")\n",
    "df = df.with_row_index(\"序号\", 1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "72ec87ef-b8cb-44d5-8d7b-29419d342b85",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "263482351d2348d787c12f70da07144b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "51ff22da-7338-45cb-bdb8-8a3f01e2b34f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (152, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>len</th><th>买卖标志</th></tr><tr><td>str</td><td>str</td><td>u32</td><td>list[str]</td></tr></thead><tbody><tr><td>&quot;600839&quot;</td><td>&quot;四川长虹&quot;</td><td>3</td><td>[&quot;买入&quot;, &quot;卖出&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;600022&quot;</td><td>&quot;山东钢铁&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;688660&quot;</td><td>&quot;电气风电&quot;</td><td>1</td><td>[&quot;买入&quot;]</td></tr><tr><td>&quot;600336&quot;</td><td>&quot;澳柯玛&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;002106&quot;</td><td>&quot;莱宝高科&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;300221&quot;</td><td>&quot;银禧科技&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;600120&quot;</td><td>&quot;浙江东方&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;002753&quot;</td><td>&quot;永东股份&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;000717&quot;</td><td>&quot;中南股份&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr><tr><td>&quot;300905&quot;</td><td>&quot;宝丽迪&quot;</td><td>2</td><td>[&quot;买入&quot;, &quot;卖出&quot;]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (152, 4)\n",
       "┌──────────┬──────────┬─────┬──────────────────────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ len ┆ 买卖标志                 │\n",
       "│ ---      ┆ ---      ┆ --- ┆ ---                      │\n",
       "│ str      ┆ str      ┆ u32 ┆ list[str]                │\n",
       "╞══════════╪══════════╪═════╪══════════════════════════╡\n",
       "│ 600839   ┆ 四川长虹 ┆ 3   ┆ [\"买入\", \"卖出\", \"卖出\"] │\n",
       "│ 600022   ┆ 山东钢铁 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 688660   ┆ 电气风电 ┆ 1   ┆ [\"买入\"]                 │\n",
       "│ 600336   ┆ 澳柯玛   ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 002106   ┆ 莱宝高科 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ …        ┆ …        ┆ …   ┆ …                        │\n",
       "│ 300221   ┆ 银禧科技 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 600120   ┆ 浙江东方 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 002753   ┆ 永东股份 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 000717   ┆ 中南股份 ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "│ 300905   ┆ 宝丽迪   ┆ 2   ┆ [\"买入\", \"卖出\"]         │\n",
       "└──────────┴──────────┴─────┴──────────────────────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.group_by(\"证券代码\", \"证券名称\").agg(pl.len(), pl.col(\"买卖标志\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "23d48288-643c-40e5-b743-81c9abeb7ed5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (152, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th><th>证券名称</th><th>结余数量</th></tr><tr><td>str</td><td>str</td><td>f64</td></tr></thead><tbody><tr><td>&quot;603167&quot;</td><td>&quot;渤海轮渡&quot;</td><td>-13600.0</td></tr><tr><td>&quot;300889&quot;</td><td>&quot;爱克股份&quot;</td><td>-7000.0</td></tr><tr><td>&quot;600333&quot;</td><td>&quot;长春燃气&quot;</td><td>-3900.0</td></tr><tr><td>&quot;300155&quot;</td><td>&quot;安居宝&quot;</td><td>0.0</td></tr><tr><td>&quot;603626&quot;</td><td>&quot;科森科技&quot;</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;600525&quot;</td><td>&quot;长园集团&quot;</td><td>8500.0</td></tr><tr><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>9600.0</td></tr><tr><td>&quot;300022&quot;</td><td>&quot;吉峰科技&quot;</td><td>10800.0</td></tr><tr><td>&quot;300215&quot;</td><td>&quot;电科院&quot;</td><td>20000.0</td></tr><tr><td>&quot;688660&quot;</td><td>&quot;电气风电&quot;</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (152, 3)\n",
       "┌──────────┬──────────┬──────────┐\n",
       "│ 证券代码 ┆ 证券名称 ┆ 结余数量 │\n",
       "│ ---      ┆ ---      ┆ ---      │\n",
       "│ str      ┆ str      ┆ f64      │\n",
       "╞══════════╪══════════╪══════════╡\n",
       "│ 603167   ┆ 渤海轮渡 ┆ -13600.0 │\n",
       "│ 300889   ┆ 爱克股份 ┆ -7000.0  │\n",
       "│ 600333   ┆ 长春燃气 ┆ -3900.0  │\n",
       "│ 300155   ┆ 安居宝   ┆ 0.0      │\n",
       "│ 603626   ┆ 科森科技 ┆ 0.0      │\n",
       "│ …        ┆ …        ┆ …        │\n",
       "│ 600525   ┆ 长园集团 ┆ 8500.0   │\n",
       "│ 300132   ┆ 青松股份 ┆ 9600.0   │\n",
       "│ 300022   ┆ 吉峰科技 ┆ 10800.0  │\n",
       "│ 300215   ┆ 电科院   ┆ 20000.0  │\n",
       "│ 688660   ┆ 电气风电 ┆ 20200.0  │\n",
       "└──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.group_by(\"证券代码\", \"证券名称\").agg(\n",
    "    结余数量=(\n",
    "        pl.when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .sum()\n",
    "    ),\n",
    ").sort(\"结余数量\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "977ec10b-18d1-438b-8eba-6dc99673e87c",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = df.join(\n",
    "    df.group_by(\"证券代码\", \"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .sum()\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\") < 0),\n",
    "    on=\"证券代码\",\n",
    "    how=\"anti\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1dd3cd7b-b748-48a1-b2af-610aa839272f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "d1ce04fc-4eb2-476b-bdf7-efc2051f8977",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df[\"交易日期\"].min()\n",
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0c9ea1d5-bb77-4e34-aeac-f10b069a13b8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date = df[\"交易日期\"].max()\n",
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "97363e67-ff4e-4e93-8951-6a7b96d61421",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th></tr><tr><td>date</td></tr></thead><tbody><tr><td>2022-07-11</td></tr><tr><td>2022-07-12</td></tr><tr><td>2022-07-13</td></tr><tr><td>2022-07-14</td></tr><tr><td>2022-07-15</td></tr><tr><td>&hellip;</td></tr><tr><td>2023-10-27</td></tr><tr><td>2023-10-28</td></tr><tr><td>2023-10-29</td></tr><tr><td>2023-10-30</td></tr><tr><td>2023-10-31</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 1)\n",
       "┌────────────┐\n",
       "│ 日期       │\n",
       "│ ---        │\n",
       "│ date       │\n",
       "╞════════════╡\n",
       "│ 2022-07-11 │\n",
       "│ 2022-07-12 │\n",
       "│ 2022-07-13 │\n",
       "│ 2022-07-14 │\n",
       "│ 2022-07-15 │\n",
       "│ …          │\n",
       "│ 2023-10-27 │\n",
       "│ 2023-10-28 │\n",
       "│ 2023-10-29 │\n",
       "│ 2023-10-30 │\n",
       "│ 2023-10-31 │\n",
       "└────────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k1 = pl.select(日期=pl.date_range(start_date, end_date))\n",
    "k1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b07556ae-3a75-40ca-8564-ea20fd4b0792",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (152, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;000096&quot;</td></tr><tr><td>&quot;000532&quot;</td></tr><tr><td>&quot;000559&quot;</td></tr><tr><td>&quot;000599&quot;</td></tr><tr><td>&quot;000655&quot;</td></tr><tr><td>&hellip;</td></tr><tr><td>&quot;688299&quot;</td></tr><tr><td>&quot;688321&quot;</td></tr><tr><td>&quot;688360&quot;</td></tr><tr><td>&quot;688393&quot;</td></tr><tr><td>&quot;688660&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (152, 1)\n",
       "┌──────────┐\n",
       "│ 证券代码 │\n",
       "│ ---      │\n",
       "│ str      │\n",
       "╞══════════╡\n",
       "│ 000096   │\n",
       "│ 000532   │\n",
       "│ 000559   │\n",
       "│ 000599   │\n",
       "│ 000655   │\n",
       "│ …        │\n",
       "│ 688299   │\n",
       "│ 688321   │\n",
       "│ 688360   │\n",
       "│ 688393   │\n",
       "│ 688660   │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k2 = df[\"证券代码\"].unique().sort().to_frame()\n",
    "k2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6ddbd156-ab0b-4e4e-9b1c-829782f56bfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "k = k1.join(k2, how=\"cross\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c2c94d20-cc78-4960-be6e-37d1c36f79aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "d2 = (\n",
    "    k.join(\n",
    "        d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    "    )\n",
    "    .sort(\"日期\", \"证券代码\")\n",
    "    .with_columns(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .otherwise(0)\n",
    "            .cum_sum()\n",
    "            .over(\"证券代码\")\n",
    "        ),\n",
    "    )\n",
    "    .filter(pl.col.结余数量 > 0)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ce100432-a12c-426b-b4fc-34df160ff362",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d552b49e00d749bdbcfd249e07d7fa5a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '证券代码', '序号', '券商', '交易时间', '证券名称', '买卖标志', '成交价格', '成…"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "8b8c1b7a-1669-488b-9dc6-e018e49f2fec",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a2e8a6fd-dd3a-4c61-a610-dfad11b0bed2",
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "428c4d5e-bc99-435f-9e89-33456db391ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231031&quot;</td><td>14.75</td><td>14.9</td><td>14.59</td><td>14.7</td><td>14.75</td><td>-0.05</td><td>-0.339</td><td>65859.96</td><td>96984.271</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231030&quot;</td><td>13.88</td><td>14.89</td><td>13.88</td><td>14.75</td><td>13.93</td><td>0.82</td><td>5.8866</td><td>123932.16</td><td>180119.372</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231027&quot;</td><td>13.7</td><td>13.98</td><td>13.51</td><td>13.93</td><td>13.64</td><td>0.29</td><td>2.1261</td><td>35782.0</td><td>49386.168</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231026&quot;</td><td>13.49</td><td>13.68</td><td>13.4</td><td>13.64</td><td>13.62</td><td>0.02</td><td>0.1468</td><td>19215.0</td><td>26005.866</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231025&quot;</td><td>13.65</td><td>13.77</td><td>13.58</td><td>13.62</td><td>13.67</td><td>-0.05</td><td>-0.3658</td><td>18484.0</td><td>25274.163</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220715&quot;</td><td>13.61</td><td>13.66</td><td>13.12</td><td>13.13</td><td>13.59</td><td>-0.46</td><td>-3.3848</td><td>32967.65</td><td>44114.064</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220714&quot;</td><td>13.54</td><td>13.75</td><td>13.5</td><td>13.59</td><td>13.54</td><td>0.05</td><td>0.3693</td><td>21967.0</td><td>29851.164</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220713&quot;</td><td>13.55</td><td>13.63</td><td>13.39</td><td>13.54</td><td>13.61</td><td>-0.07</td><td>-0.5143</td><td>22793.0</td><td>30714.624</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220712&quot;</td><td>13.65</td><td>13.69</td><td>13.41</td><td>13.61</td><td>13.65</td><td>-0.04</td><td>-0.293</td><td>29679.0</td><td>40146.31</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220711&quot;</td><td>13.2</td><td>13.96</td><td>13.07</td><td>13.65</td><td>13.18</td><td>0.47</td><td>3.566</td><td>62827.0</td><td>85869.11</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 11)\n",
       "┌───────────┬────────────┬───────┬───────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open  ┆ high  ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---   ┆ ---   ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64   ┆ f64   ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪═══════╪═══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 002462.SZ ┆ 20231031   ┆ 14.75 ┆ 14.9  ┆ … ┆ -0.05  ┆ -0.339  ┆ 65859.96  ┆ 96984.271  │\n",
       "│ 002462.SZ ┆ 20231030   ┆ 13.88 ┆ 14.89 ┆ … ┆ 0.82   ┆ 5.8866  ┆ 123932.16 ┆ 180119.372 │\n",
       "│ 002462.SZ ┆ 20231027   ┆ 13.7  ┆ 13.98 ┆ … ┆ 0.29   ┆ 2.1261  ┆ 35782.0   ┆ 49386.168  │\n",
       "│ 002462.SZ ┆ 20231026   ┆ 13.49 ┆ 13.68 ┆ … ┆ 0.02   ┆ 0.1468  ┆ 19215.0   ┆ 26005.866  │\n",
       "│ 002462.SZ ┆ 20231025   ┆ 13.65 ┆ 13.77 ┆ … ┆ -0.05  ┆ -0.3658 ┆ 18484.0   ┆ 25274.163  │\n",
       "│ …         ┆ …          ┆ …     ┆ …     ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 002462.SZ ┆ 20220715   ┆ 13.61 ┆ 13.66 ┆ … ┆ -0.46  ┆ -3.3848 ┆ 32967.65  ┆ 44114.064  │\n",
       "│ 002462.SZ ┆ 20220714   ┆ 13.54 ┆ 13.75 ┆ … ┆ 0.05   ┆ 0.3693  ┆ 21967.0   ┆ 29851.164  │\n",
       "│ 002462.SZ ┆ 20220713   ┆ 13.55 ┆ 13.63 ┆ … ┆ -0.07  ┆ -0.5143 ┆ 22793.0   ┆ 30714.624  │\n",
       "│ 002462.SZ ┆ 20220712   ┆ 13.65 ┆ 13.69 ┆ … ┆ -0.04  ┆ -0.293  ┆ 29679.0   ┆ 40146.31   │\n",
       "│ 002462.SZ ┆ 20220711   ┆ 13.2  ┆ 13.96 ┆ … ┆ 0.47   ┆ 3.566   ┆ 62827.0   ┆ 85869.11   │\n",
       "└───────────┴────────────┴───────┴───────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq = pro.daily(\n",
    "    ts_code=\"002462.SZ\",\n",
    "    start_date=format(start_date, \"%Y%m%d\"),\n",
    "    end_date=format(end_date, \"%Y%m%d\"),\n",
    ")\n",
    "hq = pl.from_pandas(hq)\n",
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5705df00-7c26-4381-be8e-2d9cd46df2e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[String,\n",
       " String,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64,\n",
       " Float64]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "de82c76f-8ce4-4807-b456-984b72b177ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_codes = (\n",
    "    d1.select(\n",
    "        证券代码=(\n",
    "            pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\", \"3\"]))\n",
    "            .then(pl.format(\"{}.SZ\", pl.col(\"证券代码\")))\n",
    "            .when(pl.col(\"证券代码\").str.head(1) == \"6\")\n",
    "            .then(pl.format(\"{}.SH\", pl.col(\"证券代码\")))\n",
    "        ),\n",
    "    )\n",
    "    .to_series()\n",
    "    .unique()\n",
    "    .sort()\n",
    "    .to_list()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4bf1d9ae-10ea-48d3-90e7-710783cc6308",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "412e8088-c640-42c7-876c-c44fceca8567",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3c97577b4aa1446882b5854178ac10fb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq = [\n",
    "    pl.from_pandas(\n",
    "        pro.daily(\n",
    "            ts_code=ts_code,\n",
    "            start_date=format(start_date, \"%Y%m%d\"),\n",
    "            end_date=format(end_date, \"%Y%m%d\"),\n",
    "        )\n",
    "    )\n",
    "    for ts_code in tqdm(ts_codes)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "986459d8-98e8-471b-b6fd-9fed562026a7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "149"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c7e7bb2b-92a3-4ef5-825e-7ebcf5673427",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.concat(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "276d2855-fe60-4def-bad0-8ffac0363706",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "abfb0016-4c18-46bd-ab9a-4215f0c51838",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.read_parquet(\"daily.parquet\")\n",
    "hq = hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6),\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "06953d93-8249-40f8-832a-d68350db0070",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 28)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th><th>vratio</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>4.08</td><td>4.13</td><td>4.04</td><td>4.12</td><td>4.06</td><td>0.06</td><td>1.4778</td><td>89030.64</td><td>36352.212</td><td>0.003864</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>3.65</td><td>3.68</td><td>3.64</td><td>3.66</td><td>3.65</td><td>0.01</td><td>0.274</td><td>370994.07</td><td>135739.515</td><td>0.001032</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.57</td><td>6.57</td><td>6.49</td><td>6.51</td><td>6.57</td><td>-0.06</td><td>-0.9132</td><td>20737.5</td><td>13537.134</td><td>0.010319</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>2.61</td><td>2.62</td><td>2.58</td><td>2.59</td><td>2.61</td><td>-0.02</td><td>-0.7663</td><td>247321.0</td><td>64052.01</td><td>0.002183</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>13.2</td><td>13.96</td><td>13.07</td><td>13.65</td><td>13.18</td><td>0.47</td><td>3.566</td><td>62827.0</td><td>85869.11</td><td>0.001655</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td><td>14.2</td><td>14.27</td><td>14.01</td><td>14.07</td><td>14.21</td><td>-0.14</td><td>-0.9852</td><td>17859.2</td><td>25247.849</td><td>0.0028</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td><td>15.81</td><td>15.93</td><td>15.65</td><td>15.78</td><td>15.8</td><td>-0.02</td><td>-0.1266</td><td>18384.12</td><td>29025.236</td><td>0.001686</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td><td>5.18</td><td>5.32</td><td>5.17</td><td>5.25</td><td>5.17</td><td>0.08</td><td>1.5474</td><td>88442.01</td><td>46306.236</td><td>0.001085</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td><td>5.87</td><td>5.94</td><td>5.86</td><td>5.91</td><td>5.87</td><td>0.04</td><td>0.6814</td><td>40253.0</td><td>23766.196</td><td>0.002087</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td><td>9.16</td><td>9.29</td><td>9.09</td><td>9.24</td><td>9.17</td><td>0.07</td><td>0.7634</td><td>43642.92</td><td>40302.885</td><td>0.001237</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 28)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬─────────┬───────────┬────────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ pct_chg ┆ vol       ┆ amount     ┆ vratio   │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---     ┆ ---       ┆ ---        ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64     ┆ f64       ┆ f64        ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪═════════╪═══════════╪════════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ 1.4778  ┆ 89030.64  ┆ 36352.212  ┆ 0.003864 │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ 0.274   ┆ 370994.07 ┆ 135739.515 ┆ 0.001032 │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -0.9132 ┆ 20737.5   ┆ 13537.134  ┆ 0.010319 │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -0.7663 ┆ 247321.0  ┆ 64052.01   ┆ 0.002183 │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ 3.566   ┆ 62827.0   ┆ 85869.11   ┆ 0.001655 │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …       ┆ …         ┆ …          ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ -0.9852 ┆ 17859.2   ┆ 25247.849  ┆ 0.0028   │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -0.1266 ┆ 18384.12  ┆ 29025.236  ┆ 0.001686 │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ 1.5474  ┆ 88442.01  ┆ 46306.236  ┆ 0.001085 │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ 0.6814  ┆ 40253.0   ┆ 23766.196  ┆ 0.002087 │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ 0.7634  ┆ 43642.92  ┆ 40302.885  ┆ 0.001237 │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴─────────┴───────────┴────────────┴──────────┘"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq, left_on=[\"交易日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    ").with_columns(\n",
    "    vratio=pl.col(\"成交数量\") / 100 / pl.col(\"vol\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "28ac1716-8ff2-438b-bcac-b6e8427d0e60",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
